﻿using ModelLayer;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DataAccessObjectLayer
{
    public class DBEdge : DBClass
    {
        private List<Station> _stationList = new List<Station>();

        /// <summary>
        /// Builds a query with a 'where'-clause.
        /// </summary>
        /// <param name="wClause">The where clause.</param>
        protected override string BuildQuery(string wClause)
        {
            String query = "SELECT * FROM edge";

            if (wClause.Length > 0)
            {
                query += " WHERE " + wClause;
            }
            return query;
        }

        /// <summary>
        /// Builds an object from either a dataset or a datareader. The bool 'connected' defines if
        /// it is one or the other
        /// </summary>
        /// <param name="dataSetOrDataReader"></param>
        /// <param name="connected"></param>
        protected override object BuildObject(object dataSetOrDataReader, bool connected)
        {
            if (!_stationList.Any())
            {
                DBStation dbStation = new DBStation();
                _stationList = dbStation.GetAllStations();
            }

            Edge edge = null;

            try
            {
                if (connected)
                {
                    SqlDataReader dataReader = dataSetOrDataReader as SqlDataReader;
                    if (dataReader != null)
                    {
                        edge = new Edge
                        {
                            //Using linq expression to get the nodes
                            FirstNode = new Node((from s in _stationList
                                                  where s.Id == (long)dataReader["FK_Station1"]
                                                  select s).FirstOrDefault()),
                            SecondNode = new Node((from s in _stationList
                                                   where s.Id == (long)dataReader["FK_Station2"]
                                                   select s).FirstOrDefault()),
                            Distance = (long)dataReader["Distance"],
                            Time = TimeSpan.FromMinutes((double)dataReader["Time"])
                        };
                    }
                }
                else
                {
                    DataRow dataRow = dataSetOrDataReader as DataRow;
                    if (dataRow != null)
                    {
                        edge = new Edge
                        {
                            FirstNode = new Node((from s in _stationList
                                                  where s.Id == (long)dataRow["FK_Station1"]
                                                  select s).FirstOrDefault()),
                            SecondNode = new Node((from s in _stationList
                                                   where s.Id == (long)dataRow["FK_Station2"]
                                                   select s).FirstOrDefault()),
                            Distance = (double)dataRow["distance"],
                            Time = TimeSpan.FromMinutes((double)dataRow["Time"])
                        };
                    }
                }
            }

            catch (Exception e)
            {
                throw new Exception("There was an error when building the edge object:\n" + e.Message);
            }

            object o = edge;
            return o;
        }

        /// <summary>
        /// Gets all edges from the database.
        /// </summary>
        public List<Edge> GetAllEdges()
        {
            List<Edge> list = new List<Edge>();
            foreach (var o in MiscWhere(""))
            {
                list.Add((Edge)o);
            }
            return list;
        }

        /// <summary>
        /// Gets the edges from a specific station.
        /// </summary>
        /// <param name="stationId">The station identifier.</param>
        public List<Edge> GetEdges(long stationId)
        {
            // DER SKAL BRUGES DENNE HER
            //SELECT FK_Station1 FROM edge WHERE FK_Station2 = 11 UNION SELECT FK_station2 FROM edge WHERE FK_station1 = 11
            string query = "FK_Station2 = " + stationId +
                           " UNION SELECT * FROM edge WHERE FK_station1 = " + stationId;
            List<Edge> list = new List<Edge>();
            foreach (var o in MiscWhere(query))
            {
                list.Add((Edge)o);
            }
            return list;
        }

        /// <summary>
        /// Creates an edge between two stations in the database.
        /// </summary>
        /// <param name="edge">The edge.</param>
        public int CreateEdge(Edge edge)
        {
            int rc = -1;
            const string query = "INSERT INTO edge(FK_Station1,FK_Station2,Distance,Time) " +
                                 "VALUES(@Station1,@Station2,@Distance,@Time)";
            //Console.WriteLine("Insert query : " + query);
            var dbCon = new DBCon();

            // insert new edge

            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdIns = SqlCommand(connection, out transaction);

                try
                {
                    cmdIns.CommandText = query;

                    using (cmdIns)
                    {
                        cmdIns.Parameters.Add(new SqlParameter("@Station1", SqlDbType.BigInt)
                        {
                            Value = edge.FirstNode.Station.Id
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@Station2", SqlDbType.BigInt)
                        {
                            Value = edge.SecondNode.Station.Id
                        });
                        cmdIns.Parameters.Add(new SqlParameter("@Distance", SqlDbType.Float) { Value = edge.Distance });
                        cmdIns.Parameters.Add(new SqlParameter("@Time", SqlDbType.Float) { Value = edge.Time.TotalMinutes });

                        rc = cmdIns.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    TryRollback(e, transaction, "Insert in DBEdge");
                }
            }
            if (rc == -1)
                throw new Exception("The edge was not inserted");
            return rc;
        }

        /// <summary>
        /// Deletes an edge in the database between to stations.
        /// </summary>
        /// <param name="station1">The station1.</param>
        /// <param name="station2">The station2.</param>
        public int DeleteEdge(long station1, long station2)
        {
            const string query = "DELETE FROM Edge " +
                                 "WHERE (FK_station1 = @station1 and FK_station2 = @station2 ) " +
                                 "OR (FK_station1 = @station2 and FK_station2 = @station1 )";
            //Console.WriteLine("Delete query:" + query);

            var dbCon = new DBCon();
            using (SqlConnection connection = (SqlConnection)dbCon.GetConnection())
            {
                SqlTransaction transaction;
                var cmdDel = SqlCommand(connection, out transaction);

                int rc;
                try
                {
                    cmdDel.CommandText = query;

                    using (cmdDel)
                    {
                        cmdDel.Parameters.Add(new SqlParameter("@Station1", SqlDbType.BigInt) { Value = station1 });
                        cmdDel.Parameters.Add(new SqlParameter("@Station2", SqlDbType.BigInt) { Value = station2 });
                        rc = cmdDel.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }

                catch (Exception e)
                {
                    TryRollback(e, transaction, "Delete in DBEge");
                    throw;
                }
                if (rc == -1)
                    throw new Exception("The edge was not deleted");
                return rc;
            }
        }
    }
}